set more off

* Panel A
* Top 10 Colleges by Mobility Rate (Top 20%) 
use "${robustness}", clear

* Combine colleges in CUNY
egen mean_success = wtmean(kq5_cond_parq1_coli) if inlist(super,7273,2688,7022, ///
	2693,2696,2687,2689,2690,4759,8611,2691,10051,2692,2697,10097,2694,2698), ///
	weight(count_coli * par_q1_coli)
keep if inlist(super,7273,2688,7022,2693,2696,2687,2689,2690,4759,8611,2691,10051,2692,2697,10097,2694,2698)
collapse (mean) mr_kq5_pq1_coli par_q1_coli kq5_cond_parq1_coli=mean_success (rawsum) count_coli [w=count_coli]
gen super_opeid = 9999
gen name = "CUNY System"
tempfile temp
save `temp'
use "${robustness}", clear
drop if super_opeid < 0
drop if inlist(super,7273,2688,7022,2693,2696,2687,2689,2690,4759,8611,2691,10051,2692,2697,10097,2694,2698)
keep super_opeid name mr_kq5_pq1_coli par_q1_coli kq5_cond_parq1_coli count_coli
append using `temp'

* Export top 10
foreach v of varlist mr_kq5_pq1_coli par_q1_coli kq5_cond_parq1_coli{
	replace `v' = `v'*100
}

cap drop _merge
merge 1:1 super_opeid using ${closed_supers}, keepusing(super_opeid)
drop if _merge == 3 | _merge == 2
drop _merge

gsort -mr_kq5_pq1_coli
preserve
keep if count_coli >= 300
keep if _n <= 30
gen rank = _n
keep super name mr_kq5_pq1_coli par_q1_coli kq5_cond_parq1_coli rank
order rank, first
list
export delimited using "${tabs}/app_table20a.csv", replace
restore

* Panel B
* Top 10 Colleges by Upper-Tail Mobility Rate (Top 1%) 
use "${robustness}", clear

* Combine colleges in CUNY
egen mean_success = wtmean(ktop1pc_cond_parq1_coli) if inlist(super,7273,2688,7022, ///
	2693,2696,2687,2689,2690,4759,8611,2691,10051,2692,2697,10097,2694,2698), ///
	weight(count_coli * par_q1_coli)
keep if inlist(super,7273,2688,7022,2693,2696,2687,2689,2690,4759,8611,2691,10051,2692,2697,10097,2694,2698)
collapse (mean) mr_ktop1_pq1_coli par_q1_coli ktop1pc_cond_parq1_coli=mean_success (rawsum) count_coli [w=count_coli]
gen super_opeid = 9999
gen name = "CUNY System"
tempfile temp
save `temp'
use "${robustness}", clear
drop if super_opeid < 0
drop if inlist(super,7273,2688,7022,2693,2696,2687,2689,2690,4759,8611,2691,10051,2692,2697,10097,2694,2698)
keep super_opeid name mr_ktop1_pq1_coli par_q1_coli ktop1pc_cond_parq1_coli count_coli
append using `temp'

* Export top 10
foreach v of varlist mr_ktop1_pq1_coli par_q1_coli ktop1pc_cond_parq1_coli{
	replace `v' = `v'*100
}

cap drop _merge
merge 1:1 super_opeid using $closed_supers, keepusing(super_opeid)
drop if _merge == 3 | _merge == 2
drop _merge

gsort -mr_ktop1_pq1_coli
preserve
keep if count_coli >= 300
keep if _n <= 30
gen rank = _n
keep  super name mr_ktop1_pq1_coli par_q1_coli ktop1pc_cond_parq1_coli rank
order rank, first
list
export delimited using "${tabs}/app_table20b.csv", replace
restore

